<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Chapter&nbsp;10.&nbsp;Built In Functions</title>
<link href="../docbook.css" type="text/css" rel="stylesheet">
<meta content="DocBook XSL-NS Stylesheets V1.76.1" name="generator">
<meta name="keywords" content="Hsqldb, HyperSQL, SQL">
<meta name="keywords" content="Hsqldb, HyperSQL, Database, JDBC, Java">
<link rel="home" href="index.html" title="HyperSQL User Guide">
<link rel="up" href="index.html" title="HyperSQL User Guide">
<link rel="prev" href="triggers-chapt.html" title="Chapter&nbsp;9.&nbsp;Triggers">
<link rel="next" href="management-chapt.html" title="Chapter&nbsp;11.&nbsp;System Management">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table summary="Navigation header" width="100%">
<tr>
<td align="left" width="30%"><a accesskey="p" href="triggers-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a>&nbsp;</td><td align="center" width="40%" style="font-weight:bold;">Chapter&nbsp;10.&nbsp;Built In Functions</td><td align="right" width="30%">&nbsp;<a accesskey="n" href="management-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td>
</tr>
<tr>
<td valign="top" align="left" width="30%">Chapter&nbsp;9.&nbsp;Triggers&nbsp;</td><td align="center" width="40%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="30%">&nbsp;Chapter&nbsp;11.&nbsp;System Management</td>
</tr>
</table>
</div>
<HR>
<div class="chapter" title="Chapter&nbsp;10.&nbsp;Built In Functions">
<div class="titlepage">
<div>
<div>
<h2 class="title">
<a name="builtinfunctions-chapt"></a>Chapter&nbsp;10.&nbsp;Built In Functions</h2>
</div>
<div>
<div class="authorgroup">
<div class="author">
<h3 class="author">
<span class="firstname">Fred</span> <span class="surname">Toussi</span>
</h3>
<div class="affiliation">
<span class="orgname">The HSQL Development Group<br>
</span>
</div>
</div>
</div>
</div>
<div>
<p class="releaseinfo">$Revision: 4903 $</p>
</div>
<div>
<div class="legalnotice" title="Legal Notice">
<a name="N1336B"></a>
<p>Copyright 2010-2012 Fred Toussi. Permission is granted to distribute
      this document without any alteration under the terms of the HSQLDB
      license. Additional permission is granted to the HSQL Development Group
      to distribute this document with or without alterations under the terms
      of the HSQLDB license.</p>
</div>
</div>
<div>
<p class="pubdate">2012-01-22 11:32:56-0500</p>
</div>
</div>
</div>
<div class="toc">
<p>
<b>Table of Contents</b>
</p>
<dl>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_overview">Overview</a></span>
</dt>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_string_binary_functions">String and Binary String Functions</a></span>
</dt>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#builtin_functions_numeric_sect">Numeric Functions</a></span>
</dt>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_datetime_functions">Date Time and Interval Functions</a></span>
</dt>
<dd>
<dl>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_timezone_functions">Functions to Report the Time Zone.</a></span>
</dt>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_current_datetime">Functions to Report the Current Datetime</a></span>
</dt>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_extract_datetime">Functions to Extract an Element of a Datetime</a></span>
</dt>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_datetime_arithmetic">Functions for Datetime Arithmetic</a></span>
</dt>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_datetime_format">Functions to Convert or Format a Datetime</a></span>
</dt>
</dl>
</dd>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_array_functions">Array Functions</a></span>
</dt>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_general_functions">General Functions</a></span>
</dt>
<dt>
<span class="section"><a href="builtinfunctions-chapt.html#bfc_system_functions">System Functions</a></span>
</dt>
</dl>
</div>
<div class="section" title="Overview">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="bfc_overview"></a>Overview</h2>
</div>
</div>
</div>
<p>HyperSQL supports a wide range of built-in functions and allows
    user-defined functions written in SQL and Java languages. User-defined
    functions are covered in the <a class="link" href="sqlroutines-chapt.html" title="Chapter&nbsp;8.&nbsp;SQL-Invoked Routines">SQL-Invoked Routines</a> chapter. If a built-in function is
    not available, you can write your own using procedural SQL or Java.
    Built-in aggregate functions are discussed in chapters that cover SQL in
    general.</p>
<p>The built-in functions fall into three groups:</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>SQL Standard Functions</p>
<p>A wide rang of functions defined by SQL/Foundation are
          supported. SQL/Foundation functions that have no parameter are
          called without empty parentheses. Functions with multiple parameters
          often use keywords instead of commas to separate the parameters.
          Many functions are overloaded. Among these, some have one or more
          optional parameters that can be omitted, while the return type of
          some functions is dependent upon the type of one of the parameters.
          The usage of SQL Standard Functions (where they can be used) is
          covered more extensively in the <a class="link" href="dataaccess-chapt.html" title="Chapter&nbsp;7.&nbsp;Data Access and Change">Data Access and Change</a> chapter</p>
</li>
<li class="listitem">
<p>JDBC Open Group CLI Functions</p>
<p>These functions were defined as an extension to the CLI
          standard, which is the basis for ODBC and JDBC and supported by many
          database products. JDBC supports an escape mechanism to specify
          function calls in SQL statements in a manner that is independent of
          the function names supported by the target database engine. For
          example <code class="literal">SELECT {fn DAYOFMONTH (dateColumn)} FROM
          myTable</code> can be used in JDBC and is translated to Standard
          SQL as <code class="literal">SELECT EXTRACT (DAY_OF_MONTH FROM dateColumn) FROM
          myTable</code> if a database engine supports the Standard syntax.
          If a database engine does not support Standard SQL, then the
          translation will be different. HyperSQL supports all the function
          names specified in the JDBC specifications as native functions.
          Therefore, there is no need to use the <code class="literal">{fn FUNC_NAME ( ...
          ) }</code> escape with HyperSQL. If a JDBC function is supported
          by the SQL Standard in a different form, the SQL Standard form is
          the preferred form to use.</p>
</li>
<li class="listitem">
<p>HyperSQL Built-In Functions</p>
<p>Several additional built-in functions are available for some
          useful operations. Some of these functions return the current
          setting for the session and the database. The General Functions
          accept arguments of different types and return values based on
          comparison between the arguments.</p>
</li>
</ul>
</div>
<p>In the BNF specification used here, words in capital letters are
    actual tokens. Syntactic elements such as expressions are enclosed in
    angle brackets. The <code class="literal">&lt;left paren&gt;</code> and
    <code class="literal">&lt;right paren&gt;</code> tokens are represented with the
    actual symbol. Optional elements are enclosed with square brackets (
    <code class="literal">&lt;left bracket&gt;</code> and <code class="literal">&lt;right
    bracket&gt;</code> ). Multiple options for a required element are
    enclosed with braces (<code class="literal"> &lt;left brace&gt;</code> and
    <code class="literal">&lt;right brace&gt;</code> )<code class="literal">.</code> Alternative
    tokens are separated with the vertical bar ( <code class="literal">&lt;vertical
    bar&gt;</code> ). At the end of each function definition, the standard
    which specifies the function is noted in parentheses as JDBC or HyperSQL,
    unless the function is in the SQL/Foundation part of the SQL
    Standard.</p>
</div>
<div class="section" title="String and Binary String Functions">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="bfc_string_binary_functions"></a>String and Binary String Functions</h2>
</div>
</div>
</div>
<p>In SQL, there are three kinds of string: character, binary and bit.
    The units are respectively characters, octets, and bits. Each kind of
    string can be in different data types. CHAR, VARCHAR and CLOB are the
    character data types. BINARY, VARBINARY and BLOB are the binary data
    types. BIT and BIT VARYING are the bit string types. In all string
    functions, the position of a unit of the string within the whole string is
    specified from 1 to the length of the whole string. In the BNF,
    <code class="literal">&lt;char value expr&gt; </code>indicates any valid SQL
    expression that evaluates to a character type. Likewise,
    <code class="literal">&lt;binary value expr&gt; </code>indicates a binary type
    and<code class="literal"> &lt;num value expr&gt; </code>indicates a numeric
    type.</p>
<a name="N133C1" class="indexterm"></a>
<p>
<span class="bold"><strong>ASCII</strong></span>
</p>
<p>
<code class="literal">ASCII ( &lt;char value expr&gt; )</code>
</p>
<p>Returns an INTEGER equal to the ASCII code value of the first
    character of <code class="literal">&lt;char value expr&gt;</code>. (JDBC)</p>
<a name="N133D2" class="indexterm"></a>
<p>
<span class="bold"><strong>BIT_LENGTH</strong></span>
</p>
<p>
<code class="literal">BIT_LENGTH ( &lt;string value expression&gt;
    )</code>
</p>
<p>BIT_LENGTH can be used with character, binary and bit strings. It
    return a BIGINT value that measures the bit length of the string.
    (Foundation)</p>
<p>See also CHARACTER_LENGTH and OCTET_LENGTH.</p>
<a name="N133E2" class="indexterm"></a>
<p>
<span class="bold"><strong>CHAR</strong></span>
</p>
<p>
<code class="literal">CHAR ( &lt;UNICODE code&gt; ) </code>
</p>
<p>The argument is an INTEGER. Returns a character string containing a
    single character that has the specified<code class="literal"> &lt;UNICODE
    code&gt;</code>, which is an integer. ASCII codes are a subset of the
    allowed values for <code class="literal">&lt;UNICODE code&gt;</code>. (JDBC)</p>
<a name="N133F6" class="indexterm"></a><a name="N133FB" class="indexterm"></a>
<p>
<span class="bold"><strong>CHARACTER_LENGTH</strong></span>
</p>
<p>
<code class="literal">{ CHAR_LENGTH | CHARACTER_LENGTH } ( &lt;char value
    expression&gt; [ USING { CHARACTERS | OCTETS } ] )</code>
</p>
<p>The CHAR_LENGTH or CHARACTER_LENGTH function can be used with
    character strings, while OCTET_LENGTH can be used with character or binary
    strings and BIT_LENGTH can be used with character, binary and bit
    strings.</p>
<p>All functions return a BIGINT value that measures the length of the
    string in the given unit. CHAR_LENGTH counts characters, OCTET_LENGTH
    counts octets and BIT_LENGTH counts bits in the string. For CHAR_LENGTH,
    if <code class="literal">[ USING OCTETS ] </code>is specified, the octet count is
    returned, which is twice the normal length. (Foundation)</p>
<a name="N1340E" class="indexterm"></a>
<p>
<span class="bold"><strong>CONCAT</strong></span>
</p>
<p>
<code class="literal">CONCAT ( &lt;char value expr 1&gt;, &lt;char value expr
    2&gt; [, ...] )</code>
</p>
<p>
<code class="literal">CONCAT ( &lt;binary value expr 1&gt;, &lt;binary value expr
    2&gt; [, ...] )</code>
</p>
<p>The arguments are character strings or binary strings. Returns a
    string formed by concatenation of the arguments. Minimum number of
    arguments is 2. Equivalent to the SQL concatenation expression
    <code class="literal">&lt;value expr 1&gt; || &lt;value expr 2&gt; [ || ...] </code>
    . (JDBC)</p>
<a name="N13422" class="indexterm"></a>
<p>
<span class="bold"><strong>DIFFERENCE</strong></span>
</p>
<p>
<code class="literal">DIFFERENCE ( &lt;char value expr 1&gt;, &lt;char value expr
    2&gt; )</code>
</p>
<p>The arguments are character strings. Converts the arguments into
    SOUNDEX codes, and returns an INTEGER between 0-4 which indicates how
    similar the two SOUNDEX value are. If the values are the same, it returns
    4, if the values have no similarity, it returns 0. In-between values are
    returned for partial similarity. (JDBC)</p>
<a name="N13430" class="indexterm"></a>
<p>
<span class="bold"><strong>INSERT</strong></span>
</p>
<p>
<code class="literal">INSERT ( &lt;char value expr 1&gt;, &lt;offset&gt;,
    &lt;length&gt;, &lt;char value expr 2&gt; )</code>
</p>
<p>Returns a character string based on <code class="literal">&lt;char value expr
    1&gt;</code> in which <code class="literal">&lt;length&gt;</code> characters have
    been removed from the <code class="literal">&lt;offset&gt;</code> position and in
    their place, the whole <code class="literal">&lt;char value expr 2&gt;</code> is
    copied. Equivalent to SQL/Foundation <code class="literal">OVERLAY( &lt;char value
    expr1&gt; PLACING &lt; char value expr2&gt; FROM &lt;offset&gt; FOR
    &lt;length&gt; )</code> . (JDBC)</p>
<a name="N1344D" class="indexterm"></a>
<p>
<span class="bold"><strong>HEXTORAW</strong></span>
</p>
<p>
<code class="literal">HEXTORAW( &lt;char value expr&gt; )</code>
</p>
<p>Returns a BINARY string formed by translation of hexadecimal digits
    and letters in the &lt;<code class="literal">char value expr&gt;</code>. Each
    character of the <code class="literal">&lt;char value expr&gt;</code> must be a
    digit or a letter in the A | B | C | D | E | F set. Each byte of the
    retired binary string is formed by translating two hex digits into one
    byte. (HyperSQL)</p>
<a name="N13461" class="indexterm"></a>
<p>
<span class="bold"><strong>LCASE</strong></span>
</p>
<p>
<code class="literal">LCASE ( &lt;char value expr&gt; ) </code>
</p>
<p>Returns a character string that is the lower case version of the
    <code class="literal">&lt;char value expr&gt;</code>. Equivalent to SQL/Foundation
    <code class="literal">LOWER (&lt;char value expr&gt;)</code>. (JDBC)</p>
<a name="N13475" class="indexterm"></a>
<p>
<span class="bold"><strong>LEFT</strong></span>
</p>
<p>
<code class="literal">LEFT ( &lt;char value expr&gt;, &lt;count&gt; )
    </code>
</p>
<p>Returns a character string consisting of the first
    <code class="literal">&lt;count&gt;</code> characters of <code class="literal">&lt;char value
    expr&gt;</code>. Equivalent to SQL/Foundation<code class="literal">
    SUBSTRING(&lt;char value expr&gt; FROM 0 FOR &lt;count&gt;)</code>.
    (JDBC)</p>
<a name="N1348C" class="indexterm"></a>
<p>
<span class="bold"><strong>LENGTH</strong></span>
</p>
<p>
<code class="literal">LENGTH ( &lt;char value expr&gt; ) </code>
</p>
<p>Returns as a BIGINT value the number of characters in
    <code class="literal">&lt;char value expr&gt;</code>. Equivalent to SQL/Foundation
    <code class="literal">CHAR_LENGTH(&lt;char value expr&gt;)</code>. (JDBC)</p>
<a name="N134A0" class="indexterm"></a>
<p>
<span class="bold"><strong>LOCATE</strong></span>
</p>
<p>
<code class="literal">LOCATE ( &lt;char value expr 1&gt;, &lt;char value expr
    2&gt; [ , &lt;offset&gt; ] ) </code>
</p>
<p>Returns as a BIGINT value the starting position of the first
    occurrence of <code class="literal">&lt;char value expr 1&gt;</code> within
    <code class="literal">&lt;char value expr 2&gt;</code>. If
    <code class="literal">&lt;offset</code>&gt; is specified, the search begins with the
    position indicated by <code class="literal">&lt;offset&gt;</code>. If the search is
    not successful, 0 is returned. Equivalent to SQL/Foundation
    <code class="literal">POSITION(&lt;char value expr 1&gt; IN &lt;char value expr
    2&gt;)</code>. Without the third argument,<code class="literal"> LOCATE</code> is
    equivalent to the SQL Standard <code class="literal">POSITION</code> function.
    (JDBC)</p>
<a name="N134C3" class="indexterm"></a>
<p>
<span class="bold"><strong>LPAD</strong></span>
</p>
<p>
<code class="literal">LPAD ( &lt;char value expr 1&gt;, &lt;length&gt; [,
    &lt;char value expr 2&gt; ] ) </code>
</p>
<p>Returns a character string with the length of
    <code class="literal">&lt;length&gt;</code> characters. The string contains the
    characters of <code class="literal">&lt;char value expr 1&gt;</code> padded to the
    left with spaces. If <code class="literal">&lt;length&gt;</code> is smaller than the
    length of the string argument, the argument is truncated. If the optional
    <code class="literal">&lt;char value expr 2&gt;</code> is specified, this string is
    used for padding, instead of spaces. (HyperSQL)</p>
<a name="N134DD" class="indexterm"></a>
<p>
<span class="bold"><strong>LTRIM</strong></span>
</p>
<p>
<code class="literal">LTRIM ( &lt;char value expr&gt; ) </code>
</p>
<p>Returns a character string based on <code class="literal">&lt;char value
    expr&gt;</code> with the leading space characters removed. Equivalent
    to SQL/Foundation <code class="literal">TRIM( LEADING ' ' FROM &lt;char value expr&gt;
    )</code>. (JDBC)</p>
<a name="N134F1" class="indexterm"></a>
<p>
<span class="bold"><strong>OCTET_LENGTH</strong></span>
</p>
<p>
<code class="literal">OCTET_LENGTH ( &lt;string value expression&gt;
    )</code>
</p>
<p>The OCTET_LENGTH function can be used with character or binary
    strings.</p>
<p>Return a BIGINT value that measures the length of the string in
    octets<code class="literal">. When used with character strings</code>, the octet
    count is returned, which is twice the normal length. (Foundation)</p>
<a name="N13504" class="indexterm"></a>
<p>
<span class="bold"><strong>OVERLAY</strong></span>
</p>
<p>
<code class="literal">OVERLAY ( &lt;char value expr 1&gt; PLACING &lt;char value
    expr 2&gt;</code>
</p>
<p>
<code class="literal">FROM &lt;start position&gt; [ FOR &lt;string length&gt; ] [
    USING CHARACTERS ] )</code>
</p>
<p>
<code class="literal">OVERLAY ( &lt;binary value expr 1&gt; PLACING &lt;binary
    value expr 2&gt;</code>
</p>
<p>
<code class="literal">FROM &lt;start position&gt; [ FOR &lt;string length&gt; ]
    )</code>
</p>
<p>The character version of OVERLAY returns a character string based on
    <code class="literal">&lt;char value expr 1&gt;</code> in which <code class="literal">&lt;string
    length&gt;</code> characters have been removed from the
    <code class="literal">&lt;start position&gt;</code> and in their place, the whole
    <code class="literal">&lt;char value expr 2&gt;</code> is copied.</p>
<p>The binary version of OVERLAY returns a binary string formed in the
    same manner as the character version. (Foundation)</p>
<a name="N13529" class="indexterm"></a>
<p>
<span class="bold"><strong>POSITION</strong></span>
</p>
<p>
<code class="literal">POSITION ( &lt;char value expr 1&gt; IN &lt;char value expr
    2&gt; [ USING CHARACTERS ] )</code>
</p>
<p>
<code class="literal">POSITION ( &lt;binary value expr 1&gt; IN &lt;binary value
    expr 2&gt; )</code>
</p>
<p>The character and binary versions of POSITION search the string
    value of the second argument for the first occurrence of the first
    argument string. If the search is successful, the position in the string
    is returned as a BIGINT. Otherwise zero is returned. (Foundation)</p>
<a name="N1353A" class="indexterm"></a>
<p>
<span class="bold"><strong>RAWTOHEX</strong></span>
</p>
<p>
<code class="literal">RAWTOHEX( &lt;binary value expr&gt; )</code>
</p>
<p>Returns a character string composed of hexadecimal digits
    representing the bytes in the <code class="literal">&lt;binary value
    expr&gt;</code>. Each byte of the <code class="literal">&lt;binary value
    expr&gt;</code> is translated into two hex digits. (HyperSQL)</p>
<a name="N1354E" class="indexterm"></a>
<p>
<span class="bold"><strong>REGEXP_MATCHES</strong></span>
</p>
<p>
<code class="literal">REGEXP_MATCHES ( &lt;char value expr&gt;, &lt;regular
    expression&gt; ) </code>
</p>
<p>Returns true if the <code class="literal">&lt;char value expr&gt;</code>
    matches the <code class="literal">&lt;regular expression&gt;</code> as a whole. The
    <code class="literal">&lt;regular expression&gt;</code> is defined according to Java
    language regular expression rules. (HyperSQL)</p>
<a name="N13565" class="indexterm"></a>
<p>
<span class="bold"><strong>REGEXP_SUBSTRING</strong></span>
</p>
<p>
<code class="literal">REGEXP_SUBSTRING ( &lt;char value expr&gt;, &lt;regular
    expression&gt; ) </code>
</p>
<p>Returns the first region in the <code class="literal">&lt;char value
    expr&gt;</code> that matches the <code class="literal">&lt;regular
    expression&gt;</code>. The <code class="literal">&lt;regular
    expression&gt;</code> is defined according to Java language regular
    expression rules. (HyperSQL)</p>
<a name="N1357C" class="indexterm"></a>
<p>
<span class="bold"><strong>REGEXP_SUBSTRING_ARRAY</strong></span>
</p>
<p>
<code class="literal">REGEXP_SUBSTRING_ARRAY ( &lt;char value expr&gt;,
    &lt;regular expression&gt; ) </code>
</p>
<p>Returns all the regions in the the <code class="literal">&lt;char value
    expr&gt;</code> that match the <code class="literal">&lt;regular
    expression&gt;</code>. The <code class="literal">&lt;regular
    expression&gt;</code> is defined according to Java language regular
    expression rules. Returns an array containing the matching regions
    (HyperSQL)</p>
<a name="N13593" class="indexterm"></a>
<p>
<span class="bold"><strong>REPEAT</strong></span>
</p>
<p>
<code class="literal">REPEAT ( &lt;char value expr&gt;, &lt;count&gt; )
    </code>
</p>
<p>Returns a character string based on<code class="literal"> &lt;char value
    expr&gt;</code>, repeated <code class="literal">&lt;count&gt;</code> times.
    (JDBC)</p>
<a name="N135A7" class="indexterm"></a>
<p>
<span class="bold"><strong>REPLACE</strong></span>
</p>
<p>
<code class="literal">REPLACE ( &lt;char value expr 1&gt;, &lt;char value expr
    2&gt; [, &lt;char value expr 3&gt; ] )</code>
</p>
<p>Returns a character string based on <code class="literal">&lt;char value expr
    1&gt;</code> where each occurrence of <code class="literal">&lt;char value expr
    2&gt;</code> has been replaced with a copy of <code class="literal">&lt;char value
    expr 3&gt;</code>. If the function is called with just two arguments,
    the &lt;char value expr 3&gt; defaults to the empty string and calling the
    function simply removes the occurrences of <code class="literal">&lt;char value expr
    2&gt;</code> from the first string.(JDBC)</p>
<a name="N135C1" class="indexterm"></a>
<p>
<span class="bold"><strong>REVERSE</strong></span>
</p>
<p>
<code class="literal">REVERSE ( &lt;char value expr&gt; )</code>
</p>
<p>Returns a character string based on <code class="literal">&lt;char value
    expr&gt;</code> with characters in the reverse order. (HyperSQL)</p>
<a name="N135D2" class="indexterm"></a>
<p>
<span class="bold"><strong>RIGHT</strong></span>
</p>
<p>
<code class="literal">RIGHT ( &lt;char value expr&gt;, &lt;count&gt; )
    </code>
</p>
<p>Returns a character string consisting of the last
    <code class="literal">&lt;count&gt;</code> characters of <code class="literal">&lt;char value
    expr&gt;</code>. (JDBC)</p>
<a name="N135E6" class="indexterm"></a>
<p>
<span class="bold"><strong>RPAD</strong></span>
</p>
<p>
<code class="literal">RPAD ( &lt;char value expr 1&gt;, &lt;length&gt; [,
    &lt;char value expr 2&gt; ] ) </code>
</p>
<p>Returns a character string with the length of
    <code class="literal">&lt;length&gt;</code> characters. The string begins with the
    characters of <code class="literal">&lt;char value expr 1&gt;</code> padded to the
    right with spaces. If <code class="literal">&lt;length&gt;</code> is smaller than
    the length of the string argument, the argument is truncated. If the
    optional <code class="literal">&lt;char value expr 2&gt;</code> is specified, this
    string is used for padding, instead of spaces. (HyperSQL)</p>
<a name="N13600" class="indexterm"></a>
<p>
<span class="bold"><strong>RTRIM</strong></span>
</p>
<p>
<code class="literal">RTRIM ( &lt;char value expr&gt; ) </code>
</p>
<p>Returns a character string based on <code class="literal">&lt;char value
    expr&gt;</code> with the trailing space characters removed. Equivalent
    to SQL/Foundation <code class="literal">TRIM(TRAILING ' ' FROM &lt;character
    string&gt;)</code>. (JDBC)</p>
<a name="N13614" class="indexterm"></a>
<p>
<span class="bold"><strong>SOUNDEX</strong></span>
</p>
<p>
<code class="literal">SOUNDEX ( &lt;char value expr&gt; ) </code>
</p>
<p>Returns a four character code representing the sound of
    <code class="literal">&lt;char value expr&gt;</code>. The US census algorithm is
    used. For example the soundex value for Washington is W252. (JDBC)</p>
<a name="N13625" class="indexterm"></a>
<p>
<span class="bold"><strong>SPACE</strong></span>
</p>
<p>
<code class="literal">SPACE ( &lt;count&gt; ) </code>
</p>
<p>Returns a character string consisting of <code class="literal">&lt;count&gt;
    </code>spaces. (JDBC)</p>
<a name="N13636" class="indexterm"></a>
<p>
<span class="bold"><strong>SUBSTR</strong></span>
</p>
<p>
<code class="literal">{ SUBSTR | SUBSTRING } ( &lt;char value expr&gt;,
    &lt;offset&gt;, &lt;length&gt; )</code>
</p>
<p>The JDBC version of SQL/Foundation <code class="literal">SUBSTRING</code>
    returns a character string that consists of
    <code class="literal">&lt;length&gt;</code> characters from <code class="literal">&lt;char value
    expr&gt; </code>starting at the <code class="literal">&lt;offset&gt;</code>
    position. (JDBC)</p>
<a name="N13650" class="indexterm"></a>
<p>
<span class="bold"><strong>SUBSTRING</strong></span>
</p>
<p>
<code class="literal">SUBSTRING ( &lt;char value expr&gt; FROM &lt;start
    position&gt; [ FOR &lt;string length&gt; ] [ USING CHARACTERS ]
    )</code>
</p>
<p>
<code class="literal">SUBSTRING ( &lt;binary value expr&gt; FROM &lt;start
    position&gt; [ FOR &lt;string length&gt; ] )</code>
</p>
<p>The character version of SUBSTRING returns a character string that
    consists of the characters of the <code class="literal">&lt;char value expr&gt;
    </code>from <code class="literal">&lt;start position&gt;</code>. If the
    optional<code class="literal"> &lt;string length&gt;</code> is specified, only
    <code class="literal">&lt;string length&gt; </code>characters are returned.</p>
<p>The binary version of SUBSTRING returns a binary string in the same
    manner. (Foundation)</p>
<a name="N1366F" class="indexterm"></a>
<p>
<span class="bold"><strong>TRIM</strong></span>
</p>
<p>
<code class="literal">TRIM ([ [ LEADING | TRAILING | BOTH ] [ &lt;trim
    character&gt; ] FROM ] &lt;char value expr&gt; )</code>
</p>
<p>
<code class="literal">TRIM ([ [ LEADING | TRAILING | BOTH ] [ &lt;trim octet&gt;
    ] FROM ] &lt;binary value expr&gt; )</code>
</p>
<p>The character version of TRIM returns a character string based on
    <code class="literal">&lt;char value expr&gt;</code>. Consecutive instances of
    <code class="literal">&lt;trim character&gt; </code>are removed from the beginning,
    the end or both ends of the<code class="literal">&lt;char value expr&gt;
    </code>depending on the value of the optional first qualifier
    <code class="literal">[ LEADING | TRAILING | BOTH ]</code>. If no qualifier is
    specified, <code class="literal">BOTH </code>is used as default. If <code class="literal">[
    &lt;trim character&gt; ]</code> is not specified, the space character
    is used as default.</p>
<p>The binary version of TRIM returns a binary string based on
    <code class="literal">&lt;binary value expr&gt;</code>. Consecutive instances of
    <code class="literal">&lt;trim octet&gt; </code>are removed in the same manner as in
    the character version. If<code class="literal"> [ &lt;trim octet&gt; ]</code> is not
    specified, the 0 octet is used as default. (Foundation)</p>
<a name="N1369D" class="indexterm"></a>
<p>
<span class="bold"><strong>UCASE</strong></span>
</p>
<p>
<code class="literal">UCASE ( &lt;char value expr&gt; ) </code>
</p>
<p>Returns a character string that is the upper case version of the
    <code class="literal">&lt;char value expr&gt;</code>. Equivalent to SQL/Foundation
    <code class="literal">UPPER( &lt;char value expr&gt; )</code> . (JDBC)</p>
<a name="N136B1" class="indexterm"></a>
<p>
<span class="bold"><strong>UPPER</strong></span>
</p>
<p>
<code class="literal">UPPER ( &lt;char value expr&gt; ) </code>
</p>
<p>Returns a character string that is the upper case version of the
    <code class="literal">&lt;char value expr&gt;</code> . (Foundation)</p>
</div>
<div class="section" title="Numeric Functions">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="builtin_functions_numeric_sect"></a>Numeric Functions</h2>
</div>
</div>
</div>
<a name="N136C6" class="indexterm"></a>
<p>
<span class="bold"><strong>ABS</strong></span>
</p>
<p>
<code class="literal">ABS ( &lt;num value expr&gt; | &lt;interval value expr&gt;
    ) </code>
</p>
<p>Returns the absolute value of the argument as a value of the same
    type. (JDBC and Foundation)</p>
<a name="N136D4" class="indexterm"></a>
<p>
<span class="bold"><strong>ACOS</strong></span>
</p>
<p>
<code class="literal">ACOS ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the arc-cosine of the argument in radians as a value of
    DOUBLE type. (JDBC)</p>
<a name="N136E2" class="indexterm"></a>
<p>
<span class="bold"><strong>ASIN</strong></span>
</p>
<p>
<code class="literal">ASIN ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the arc-sine of the argument in radians as a value of DOUBLE
    type. (JDBC)</p>
<a name="N136F0" class="indexterm"></a>
<p>
<span class="bold"><strong>ATAN</strong></span>
</p>
<p>
<code class="literal">ATAN ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the arc-tangent of the argument in radians as a value of
    DOUBLE type. (JDBC)</p>
<a name="N136FE" class="indexterm"></a>
<p>
<span class="bold"><strong>ATAN2</strong></span>
</p>
<p>
<code class="literal">ATAN2 ( &lt;num value expr 1&gt;, &lt;num value expr 2&gt;
    ) </code>
</p>
<p>The <code class="literal">&lt;num value expr 1&gt;</code> and <code class="literal">&lt;num
    value expr 2&gt;</code> express the <code class="varname">x</code> and
    <code class="varname">y</code> coordinates of a point. Returns the angle, in
    radians, representing the angle coordinate of the point in polar
    coordinates, as a value of DOUBLE type. (JDBC)</p>
<a name="N13718" class="indexterm"></a>
<p>
<span class="bold"><strong>CEILING</strong></span>
</p>
<p>
<code class="literal">{ CEIL | CEILING } ( &lt;num value expr&gt; )
    </code>
</p>
<p>Returns the smallest integer greater than or equal to the argument.
    If the argument is exact numeric then the result is exact numeric with a
    scale of 0. If the argument is approximate numeric, then the result is of
    DOUBLE type. (JDBC and Foundation)</p>
<a name="N13726" class="indexterm"></a>
<p>
<span class="bold"><strong>BITAND</strong></span>
</p>
<p>
<code class="literal">BITAND ( &lt;num value expr 1&gt;, &lt;num value expr 2&gt;
    )</code>
</p>
<p>
<code class="literal">BITAND ( &lt;bit value expr 1&gt;, &lt;bit value expr 2&gt;
    )</code>
</p>
<a name="N13735" class="indexterm"></a>
<p>
<span class="bold"><strong>BITANDNOT</strong></span>
</p>
<p>
<code class="literal">BITANDNOT ( &lt;num value expr 1&gt;, &lt;num value expr
    2&gt; )</code>
</p>
<p>
<code class="literal">BITANDNOT ( &lt;bit value expr 1&gt;, &lt;bit value expr
    2&gt; )</code>
</p>
<a name="N13744" class="indexterm"></a>
<p>
<span class="bold"><strong>BITNOT</strong></span>
</p>
<p>
<code class="literal">BITNOT ( &lt;num value expr 1&gt; )</code>
</p>
<p>
<code class="literal">BITNOT ( &lt;bit value expr 1&gt; )</code>
</p>
<a name="N13753" class="indexterm"></a>
<p>
<span class="bold"><strong>BITOR</strong></span>
</p>
<p>
<code class="literal">BITOR ( &lt;num value expr 1&gt;, &lt;num value expr 2&gt;
    )</code>
</p>
<p>
<code class="literal">BITOR ( &lt;bit value expr 1&gt;, &lt;bit value expr 2&gt;
    )</code>
</p>
<a name="N13762" class="indexterm"></a>
<p>
<span class="bold"><strong>BITXOR</strong></span>
</p>
<p>
<code class="literal">BITXOR ( &lt;num value expr 1&gt;, &lt;num value expr 2&gt;
    )</code>
</p>
<p>
<code class="literal">BITXOR ( &lt;bit value expr 1&gt;, &lt;bit value expr 2&gt;
    )</code>
</p>
<p>These functions bit operations on two values, or in the case of
    BITNOT on a single values. The values are either integer values, or bit
    strings. The result is an integer value of the same type as the arguments,
    or a bit string of the same length as the argument. Each bit of the result
    is formed by performing the operation on corresponding bits of the
    arguments. The names of the function indicate NOT, OR, AND, XOR
    operations. The BITANDNOT performs NOT on the second argument, then
    performs AND on result and the first argument. (HyperSQL)</p>
<a name="N13773" class="indexterm"></a>
<p>
<span class="bold"><strong>COS</strong></span>
</p>
<p>
<code class="literal">COS ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the cosine of the argument (an angle expressed in radians)
    as a value of DOUBLE type. (JDBC)</p>
<a name="N13781" class="indexterm"></a>
<p>
<span class="bold"><strong>COT</strong></span>
</p>
<p>
<code class="literal">COT ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the cotangent of the argument as a value of DOUBLE type. The
    <code class="literal">&lt;num value expr&gt;</code> represents an angle expressed in
    radians. (JDBC)</p>
<a name="N13792" class="indexterm"></a>
<p>
<span class="bold"><strong>DEGREES</strong></span>
</p>
<p>
<code class="literal">DEGREES ( &lt;num value expr&gt; ) </code>
</p>
<p>Converts the argument (an angle expressed in<code class="literal">
    radians</code>) into degrees and returns the value in the DOUBLE type.
    (JDBC)</p>
<a name="N137A3" class="indexterm"></a>
<p>
<span class="bold"><strong>EXP</strong></span>
</p>
<p>
<code class="literal">EXP ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the exponential value of the argument as a value of DOUBLE
    type. (JDBC and Foundation)</p>
<a name="N137B1" class="indexterm"></a>
<p>
<span class="bold"><strong>FLOOR</strong></span>
</p>
<p>
<code class="literal">FLOOR ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the largest integer that is less than or equal to the
    argument. If the argument is exact numeric then the result is exact
    numeric with a scale of 0. If the argument is approximate numeric, then
    the result is of DOUBLE type. (JDBC and Foundation)</p>
<a name="N137BF" class="indexterm"></a>
<p>
<span class="bold"><strong>LN</strong></span>
</p>
<p>
<code class="literal">LN ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the natural logarithm of the argument, as a value of DOUBLE
    type. (Foundation)</p>
<a name="N137CD" class="indexterm"></a>
<p>
<span class="bold"><strong>LOG</strong></span>
</p>
<p>
<code class="literal">LOG ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the natural logarithm of the argument, as a value of DOUBLE
    type. (JDBC)</p>
<a name="N137DB" class="indexterm"></a>
<p>
<span class="bold"><strong>LOG10</strong></span>
</p>
<p>
<code class="literal">LOG10 ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the base 10 logarithm of the argument as a value of DOUBLE
    type. (JDBC)</p>
<p>
<code class="literal">MOD ( &lt;num value expr 1&gt;, &lt;num value expr 2&gt; )
    </code>
</p>
<a name="N137EC" class="indexterm"></a>
<p>
<span class="bold"><strong>MOD</strong></span>
</p>
<p>Returns the remainder (modulus) of <code class="literal">&lt;num value expr
    1&gt;</code> divided by <code class="literal">&lt;num value expr 2&gt;.</code>
    The data type of the returned value is the same as the second argument.
    (JDBC and Foundation)</p>
<a name="N137FD" class="indexterm"></a>
<p>
<span class="bold"><strong>PI</strong></span>
</p>
<p>
<code class="literal">PI () </code>
</p>
<p>Returns the constant pi as a value of DOUBLE type. (JDBC)</p>
<a name="N1380B" class="indexterm"></a>
<p>
<span class="bold"><strong>POWER</strong></span>
</p>
<p>
<code class="literal">POWER ( &lt;num value expr 1&gt;, &lt;num value expr 2&gt;
    ) </code>
</p>
<p>Returns the value of <code class="literal">&lt;num value expr 1&gt;</code>
    raised to the power of <code class="literal">&lt;int value expr 2&gt;</code> as a
    value of DOUBLE type. (JDBC and Foundation)</p>
<a name="N1381F" class="indexterm"></a>
<p>
<span class="bold"><strong>RADIANS</strong></span>
</p>
<p>
<code class="literal">RADIANS ( &lt;num value expr&gt; ) </code>
</p>
<p>Converts the argument (an angle expressed in<code class="literal">
    degrees</code>) into radians and returns the value in the DOUBLE type.
    (JDBC)</p>
<a name="N13830" class="indexterm"></a>
<p>
<span class="bold"><strong>RAND</strong></span>
</p>
<p>
<code class="literal">RAND ( [ &lt;int value expr&gt; ] ) </code>
</p>
<p>Returns a random value in the DOUBLE type. The optional <code class="literal">[
    &lt;int value expr&gt; ]</code> is used as seed value. In HyperSQL each
    session has a separate random number generator. The first call that uses a
    seed parameter sets the seed for subsequent calls that do not include a
    parameter. (JDBC)</p>
<a name="N13841" class="indexterm"></a>
<p>
<span class="bold"><strong>ROUND</strong></span>
</p>
<p>
<code class="literal">ROUND ( &lt;num value expr&gt;, &lt;int value expr&gt; )
    </code>
</p>
<p>The <code class="literal">&lt;num value expr&gt; </code>is of the DOUBLE type
    or DECIMAL type. The function returns a DOUBLE or DECIMAL value which is
    the value of the argument rounded to <code class="literal">&lt;int value
    expr&gt;</code> places right of the decimal point. If <code class="literal">&lt;int
    value expr&gt;</code> is negative, the first argument is rounded to
    <code class="literal">&lt;int value expr&gt;</code> places to the left of the
    decimal point.</p>
<p>This function rounds values ending with .5 or larger away from zero
    for DECIMAL arguments and results. When the value ends with .5 or larger
    and the argument and result are DOUBLE, It rounds the value towards the
    closest even value.</p>
<p>The datetime version is discussed in the next section. (JDBC)</p>
<a name="N1385F" class="indexterm"></a>
<p>
<span class="bold"><strong>SIGN</strong></span>
</p>
<p>
<code class="literal">SIGN ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns an INTEGER, indicating the sign of the argument. If the
    argument is negative then -1 is returned. If it is equal to zero then 0 is
    returned. If the argument is positive then 1 is returned. (JDBC)</p>
<a name="N1386D" class="indexterm"></a>
<p>
<span class="bold"><strong>SIN</strong></span>
</p>
<p>
<code class="literal">SIN ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the sine of the argument (an angle expressed in radians) as
    a value of DOUBLE type. (JDBC)</p>
<a name="N1387B" class="indexterm"></a>
<p>
<span class="bold"><strong>SQRT</strong></span>
</p>
<p>
<code class="literal">SQRT ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the square root of the argument as a value of DOUBLE type.
    (JDBC and Foundation)</p>
<a name="N13889" class="indexterm"></a>
<p>
<span class="bold"><strong>TAN</strong></span>
</p>
<p>
<code class="literal">TAN ( &lt;num value expr&gt; ) </code>
</p>
<p>Returns the tangent of the argument (an angle expressed in radians)
    as a value of DOUBLE type. (JDBC)</p>
<a name="N13897" class="indexterm"></a>
<p>
<span class="bold"><strong>TRUNC</strong></span>
</p>
<p>
<code class="literal">TRUNC ( &lt;num value expr&gt; [, &lt;int value expr&gt;] )
    </code>
</p>
<p>This is a similar to the <code class="literal">TRUNCATE</code> function when
    the first argument is numeric. If the second argument is omitted, zero is
    used in its place.</p>
<p>The datetime version is discussed in the next section.
    (HyperSQL)</p>
<a name="N138AA" class="indexterm"></a>
<p>
<span class="bold"><strong>TRUNCATE</strong></span>
</p>
<p>
<code class="literal">TRUNCATE ( &lt;num value expr&gt; [, &lt;int value
    expr&gt;] ) </code>
</p>
<p>Returns a value in the same type as <code class="literal">&lt;num value
    expr&gt;</code> but may reduce the scale of DECIMAL and NUMERIC values.
    The value is rounded by replacing digits with zeros from <code class="literal">&lt;int
    value expr&gt;</code> places right of the decimal point to the end. If
    <code class="literal">&lt;int value expr&gt;</code> is negative, <code class="literal">ABS(
    &lt;int value expr&gt; )</code> digits to left of the decimal point and
    all digits to the right of the decimal points are replaced with zeros.
    Results of calling TRUNCATE with 12345.6789 with (-2, 0, 2, 4) are (12300,
    12345, 12345.67, 12345.6789). The function does not change the number if
    the second argument is larger than or equal to the scale of the first
    argument.</p>
<p>If the second argument is not a constant (when it is a parameter or
    column reference) then the type of the return value is always the same as
    the type of the first argument. In this case, the discarded digits are
    replaced with zeros. (JDBC)</p>
<a name="N138C6" class="indexterm"></a>
<p>
<span class="bold"><strong>WIDTH_BUCKET</strong></span>
</p>
<p>
<code class="literal">WIDTH_BUCKET ( &lt;value expr 1&gt; , &lt;value expr 2&gt;,
    &lt;value expr 3&gt;, &lt;int value expr&gt; ) </code>
</p>
<p>Returns an integer value between 0 and <code class="literal">&lt;int value
    expr&gt; + 1</code>. The initial three parameters are of the same
    numeric or datetime type. The range, ( <code class="literal">&lt;value expr 2&gt; ,
    &lt;value expr 3&gt;</code> ) is divided into <code class="literal">&lt;int value
    expr&gt;</code> equal sections (buckets). The returned integer value
    indicates the index of the bucket where <code class="literal">&lt;value expr
    1&gt;</code> can be placed. If the <code class="literal">&lt;value expr
    1&gt;</code> falls before or after the range, the return value is 0 or
    <code class="literal">&lt;value expr 1&gt; + 1</code> respectively.</p>
<p>This function can be used with numeric or datetime values. Invalid
    arguments, including <code class="literal">&lt;int value expr&gt;</code> smaller
    than 1, or equal values for <code class="literal">&lt;value expr 2&gt;</code> and
    <code class="literal">&lt;value expr 3&gt;</code> will cause an exception.
    (Foundation)</p>
<p>An example is given below:</p>
<pre class="programlisting">WIDTH_BUCKET( 5, 10, 110, 10)
0

WIDTH_BUCKET( 23, 10, 110, 10)
2

WIDTH_BUCKET( 100, 10, 110, 10)
10

WIDTH_BUCKET( 200, 10, 110, 10)
11
</pre>
</div>
<div class="section" title="Date Time and Interval Functions">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="bfc_datetime_functions"></a>Date Time and Interval Functions</h2>
</div>
</div>
</div>
<p>Functions to report the time zone.</p>
<div class="section" title="Functions to Report the Time Zone.">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="bfc_timezone_functions"></a>Functions to Report the Time Zone.</h3>
</div>
</div>
</div>
<a name="N138FF" class="indexterm"></a>
<p>
<span class="bold"><strong>TIMEZONE</strong></span>
</p>
<p>
<code class="literal">TIMEZONE()</code>
</p>
<p>Returns the current time zone for the session. Returns an INTERVAL
      HOUR TO MINUTE value. (HyperSQL)</p>
<a name="N1390D" class="indexterm"></a>
<p>
<span class="bold"><strong>SESSION_TIMEZONE</strong></span>
</p>
<p>
<code class="literal">SESSION_TIMEZONE()</code>
</p>
<p>Returns the default time zone for the current session. Returns an
      INTERVAL HOUR TO MINUTE value. (HyperSQL)</p>
<a name="N1391B" class="indexterm"></a>
<p>
<span class="bold"><strong>DATABASE_TIMEZONE</strong></span>
</p>
<p>
<code class="literal">DATABASE_TIMEZONE()</code>
</p>
<p>Returns the time zone for the database engine. This is based on
      where the database server process is located. Returns an INTERVAL HOUR
      TO MINUTE value. (HyperSQL)</p>
</div>
<div class="section" title="Functions to Report the Current Datetime">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="bfc_current_datetime"></a>Functions to Report the Current Datetime</h3>
</div>
</div>
</div>
<a name="N1392D" class="indexterm"></a>
<p>
<span class="bold"><strong>CURRENT_DATE</strong></span>
</p>
<p>
<code class="literal">CURRENT_DATE</code>
</p>
<a name="N13939" class="indexterm"></a>
<p>
<span class="bold"><strong>CURRENT_TIME</strong></span>
</p>
<p>
<code class="literal">CURRENT_TIME [ ( &lt;time precision&gt; )
      ]</code>
</p>
<a name="N13945" class="indexterm"></a>
<p>
<span class="bold"><strong>LOCALTIME</strong></span>
</p>
<p>
<code class="literal">LOCALTIME [ ( &lt;time precision&gt; ) ]</code>
</p>
<a name="N13951" class="indexterm"></a>
<p>
<span class="bold"><strong>CURRENT_TIMESTAMP</strong></span>
</p>
<p>
<code class="literal">CURRENT_TIMESTAMP [ ( &lt;timestamp precision&gt; )
      ]</code>
</p>
<a name="N1395D" class="indexterm"></a>
<p>
<span class="bold"><strong>LOCALTIMESTAMP</strong></span>
</p>
<p>
<code class="literal">LOCALTIMESTAMP [ ( &lt;timestamp precision&gt; )
      ]</code>
</p>
<p>These datetime functions return the datetime value representing
      the moment the function is called. CURRENT_DATE returns a value of DATE
      type. CURRENT_TIME returns a value of TIME WITH TIME ZONE type.
      LOCALTIME returns a value of TIME type. CURRENT_TIMESTAMP returns a
      value of TIMESTAMP WITH TIME ZONE type. LOCALTIMESTAMP returns a value
      of TIMESTAMP type. If the optional <code class="literal">[ ( &lt;time precision&gt; )
      ]</code> or<code class="literal"> [ ( &lt;timestamp precision&gt; ) ]</code> is
      used, then the returned value has the specified fraction of the second
      precision. (Foundation)</p>
<a name="N13971" class="indexterm"></a>
<p>
<span class="bold"><strong>NOW</strong></span>
</p>
<p>
<code class="literal">NOW ()</code>
</p>
<p>This function is equivalent to <code class="literal">LOCALTIMESTAMP. It can be
      used as a no-arg function as the empty parentheses are optional.
      (HyperSQL)</code>
</p>
<a name="N13981" class="indexterm"></a>
<p>
<span class="bold"><strong>CURDATE</strong></span>
</p>
<p>
<code class="literal">CURDATE ()</code>
</p>
<p>This function is equivalent to<code class="literal"> CURRENT_DATE.
      </code>(JDBC)</p>
<a name="N13992" class="indexterm"></a>
<p>
<span class="bold"><strong>CURTIME</strong></span>
</p>
<p>
<code class="literal">CURTIME ()</code>
</p>
<p>This function is equivalent to<code class="literal"> LOCALTIME</code>.
      (JDBC)</p>
<a name="N139A3" class="indexterm"></a>
<p>
<span class="bold"><strong>SYSDATE</strong></span>
</p>
<p>
<code class="literal">SYSDATE</code>
</p>
<p>This function is equivalent to<code class="literal"> LOCALTIMESTAMP.
      </code>(HyperSQL)</p>
<a name="N139B4" class="indexterm"></a>
<p>
<span class="bold"><strong>SYSTIMESTAMP</strong></span>
</p>
<p>
<code class="literal">SYSTIMESTAMP</code>
</p>
<p>This no-arg function is equivalent to<code class="literal"> CURRENT_TIMESTAMP
      and is enabled in Oracle syntax mode only. </code>(HyperSQL)</p>
<a name="N139C5" class="indexterm"></a>
<p>
<span class="bold"><strong>TODAY</strong></span>
</p>
<p>
<code class="literal">TODAY</code>
</p>
<p>This no-arg function is equivalent to<code class="literal"> CURRENT_DATE.
      </code>(HyperSQL)</p>
</div>
<div class="section" title="Functions to Extract an Element of a Datetime">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="bfc_extract_datetime"></a>Functions to Extract an Element of a Datetime</h3>
</div>
</div>
</div>
<a name="N139DA" class="indexterm"></a>
<p>
<span class="bold"><strong>DAYNAME</strong></span>
</p>
<p>
<code class="literal">DAYNAME ( &lt;datetime value expr&gt; )</code>
</p>
<p>This function is equivalent to<code class="literal"> EXTRACT ( DAY_NAME FROM
      ... ) </code>Returns a string in the range of Sunday - Saturday.
      (JDBC)</p>
<a name="N139EB" class="indexterm"></a>
<p>
<span class="bold"><strong>DAYOFMONTH</strong></span>
</p>
<p>
<code class="literal">DAYOFMONTH ( &lt;datetime value expr&gt;
      )</code>
</p>
<p>This function is equivalent to<code class="literal"> EXTRACT ( DAY_OF_MONTH
      FROM ... ) </code>Returns an integer value in the range of 1-31.
      (JDBC)</p>
<a name="N139FC" class="indexterm"></a>
<p>
<span class="bold"><strong>DAYOFWEEK</strong></span>
</p>
<p>
<code class="literal">DAYOFWEEK ( &lt;datetime value expr&gt;
      )</code>
</p>
<p>This function is equivalent to <code class="literal">EXTRACT ( DAY_OF_WEEK FROM
      ... ) </code>Returns an integer value in the range of 1-7. The first
      day of the week is Sunday. (JDBC)</p>
<a name="N13A0D" class="indexterm"></a>
<p>
<span class="bold"><strong>DAYOFYEAR</strong></span>
</p>
<p>
<code class="literal">DAYOFYEAR ( &lt;datetime value expr&gt;
      )</code>
</p>
<p>This function is equivalent to <code class="literal">EXTRACT ( DAY_OF_YEAR FROM
      ... ) </code>Returns an integer value in the range of 1-366.
      (JDBC)</p>
<a name="N13A1E" class="indexterm"></a>
<p>
<span class="bold"><strong>DAYS</strong></span>
</p>
<p>
<code class="literal">DAYS ( &lt;datetime value expr&gt; ) </code>
</p>
<p>The <code class="literal">&lt;datetime value expr&gt; </code>is of DATE or
      TIMESTAMP type. This function returns the DAY number since the first day
      of the calendar. The first day is numbered 1. (HyperSQL)</p>
<a name="N13A2F" class="indexterm"></a>
<p>
<span class="bold"><strong>HOUR</strong></span>
</p>
<p>
<code class="literal">HOUR ( &lt;datetime value expr&gt; )</code>
</p>
<p>This function is equivalent to <code class="literal">EXTRACT ( HOUR FROM ... )
      </code>Returns an integer value in the range of 0-23. (JDBC)</p>
<a name="N13A40" class="indexterm"></a>
<p>
<span class="bold"><strong>MINUTE</strong></span>
</p>
<p>
<code class="literal">MINUTE ( &lt;datetime value expr&gt; )</code>
</p>
<p>This function is equivalent to<code class="literal"> EXTRACT ( MINUTE FROM ...
      ) </code>Returns an integer value in the range of 0 - 59.
      (JDBC)</p>
<a name="N13A51" class="indexterm"></a>
<p>
<span class="bold"><strong>MONTH</strong></span>
</p>
<p>
<code class="literal">MONTH ( &lt;datetime value expr&gt; )</code>
</p>
<p>This function is equivalent to <code class="literal">EXTRACT ( MONTH FROM ... )
      </code>Returns an integer value in the range of 1-12. (JDBC)</p>
<a name="N13A62" class="indexterm"></a>
<p>
<span class="bold"><strong>MONTHNAME</strong></span>
</p>
<p>
<code class="literal">MONTHNAME ( &lt;datetime value expr&gt;
      )</code>
</p>
<p>This function is equivalent to <code class="literal">EXTRACT ( NAME_OF_MONTH
      FROM ... ) </code>Returns a string in the range of January -
      December. (JDBC)</p>
<a name="N13A73" class="indexterm"></a>
<p>
<span class="bold"><strong>QUARTER</strong></span>
</p>
<p>
<code class="literal">QUARTER ( &lt;datetime value expr&gt; )</code>
</p>
<p>This function is equivalent to <code class="literal">EXTRACT ( QUARTER FROM ...
      ) </code>Returns an integer in the range of 1 - 4. (JDBC)</p>
<a name="N13A84" class="indexterm"></a>
<p>
<span class="bold"><strong>SECOND</strong></span>
</p>
<p>
<code class="literal">SECOND ( &lt;datetime value expr&gt; )</code>
</p>
<p>This function is equivalent to <code class="literal">EXTRACT ( SECOND FROM ...
      ) </code>Returns an integer or decimal in the range of 0 - 59, with
      the same precision as the &lt;datetime value expr&gt;. (JDBC)</p>
<a name="N13A95" class="indexterm"></a>
<p>
<span class="bold"><strong>SECONDS_SINCE_MIDNIGHT</strong></span>
</p>
<p>
<code class="literal">SECONDS_SINCE_MIDNIGHT ( &lt;datetime value expr&gt;
      )</code>
</p>
<p>This function is equivalent to<code class="literal"> EXTRACT (
      SECONDS_SINCE_MIDNIGHT FROM ... ) </code>Returns an integer in the
      range of 0 - 86399. (HyperSQL)</p>
<a name="N13AA6" class="indexterm"></a>
<p>
<span class="bold"><strong>UNIX_TIMESTAMP</strong></span>
</p>
<p>
<code class="literal">UNIX_TIMESTAMP ( [ &lt;datetime value expression&gt; ] )
      </code>
</p>
<p>This function returns a BIGINT value. With no parameter, it
      returns the number of seconds since 1970-01-01. With a DATE or TIMESTAMP
      parameter, it converts the argument into number of seconds since
      1970-01-01. The TIMESTAMP ( &lt;num value expression&gt; function
      returns a TIMESTAMP from a Unix timestamp. (HyperSQL)</p>
<a name="N13AB4" class="indexterm"></a>
<p>
<span class="bold"><strong>WEEK</strong></span>
</p>
<p>
<code class="literal">WEEK ( &lt;datetime value expr&gt; )</code>
</p>
<p>This function is equivalent to<code class="literal"> EXTRACT ( WEEK_OF_YEAR
      FROM ... ) </code>Returns an integer in the range of 1 - 54.
      (JDBC)</p>
<a name="N13AC5" class="indexterm"></a>
<p>
<span class="bold"><strong>YEAR</strong></span>
</p>
<p>
<code class="literal">YEAR ( &lt;datetime value expr&gt; )</code>
</p>
<p>This function is equivalent to<code class="literal"> EXTRACT ( YEAR FROM ... )
      </code>Returns an integer in the range of 1 - 9999. (JDBC)</p>
<a name="N13AD6" class="indexterm"></a>
<p>
<span class="bold"><strong>EXTRACT</strong></span>
</p>
<p>
<code class="literal">EXTRACT ( &lt;extract field&gt; FROM &lt;extract
      source&gt; )</code>
</p>
<p>
<code class="literal">&lt;extract field&gt; ::= YEAR | MONTH | DAY | HOUR |
      MINUTE | DAY_OF_WEEK | WEEK_OF_YEAR | QUARTER | DAY_OF_YEAR |
      DAY_OF_MONTH |</code>
</p>
<p>
<code class="literal">TIMEZONE_HOUR | TIMEZONE_MINUTE | SECOND |
      SECONDS_SINCE_MIDNIGHT |</code>
</p>
<p>
<code class="literal">DAY_NAME | MONTH_NAME</code>
</p>
<p>
<code class="literal">&lt;extract source&gt; ::= &lt;datetime value expr&gt; |
      &lt;interval value expr&gt;</code>
</p>
<p>The EXTRACT function returns a field or element of the
      <code class="literal">&lt;extract source&gt;</code>. The <code class="literal">&lt;extract
      source&gt;</code> is a datetime or interval expression. The type of
      the return value is BIGINT for most of the<code class="literal"> &lt;extract
      field&gt;</code> options. The exceptions is <code class="literal">SECOND
      </code>where a DECIMAL value is returned which has the same precision
      as the datetime or interval expression. The field values
      <code class="literal">DAY_NAME </code>or<code class="literal"> MONTH_NAME </code>result in a
      character string. When <code class="literal">MONTH_NAME</code> is specified, a
      string in the range January - December is returned. When
      <code class="literal">DAY_NAME </code>is specified, a string in the range Sunday
      -Saturday is returned.</p>
<p>If the <code class="literal">&lt;extract source&gt;</code> is <code class="literal">FROM
      &lt;datetime value expr&gt;</code>, different groups of
      <code class="literal">&lt;extract source&gt;</code> can be used depending on the
      data type of the expression. The <code class="literal">TIMEZONE_HOUR |
      TIMEZONE_MINUTE</code> options are valid only for TIME WITH TIMEZONE
      and TIMESTAMP WITH TIMEZONE data types. The <code class="literal">HOUR | MINUTE |
      SECOND | SECONDS_MIDNIGHT</code> options, are valid for TIME and
      TIMESTAMP types. The rest of the fields are valid for DATE and TIMESTAMP
      types.</p>
<p>If the <code class="literal">&lt;extract source&gt;</code> is <code class="literal">FROM
      &lt;interval value expr&gt;</code>, the <code class="literal">&lt;extract
      field&gt;</code> must be one of the fields of the INTERVAL type of
      the expressions. The <code class="literal">YEAR | MONTH</code> options may be
      valid for INTERVAL types based on months. The <code class="literal">DAY | HOUR |
      MINUTE | SECOND | SECONDS_MIDNIGHT</code> options may be valid for
      INTERVAL types based on seconds. For example,<code class="literal"> DAY | HOUR |
      MINUTE</code> are the only valid fields for the INTERVAL DAY TO
      MINUTE data type. (Foundation with HyperSQL extensions)</p>
</div>
<div class="section" title="Functions for Datetime Arithmetic">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="bfc_datetime_arithmetic"></a>Functions for Datetime Arithmetic</h3>
</div>
</div>
</div>
<a name="N13B31" class="indexterm"></a>
<p>
<span class="bold"><strong>TIMESTAMPADD</strong></span>
</p>
<p>
<code class="literal">TIMESTAMPADD ( &lt;tsi datetime field&gt;, &lt;numeric
      value expression&gt;, &lt;datetime value expr&gt;)</code>
</p>
<a name="N13B3D" class="indexterm"></a>
<p>
<span class="bold"><strong>TIMESTAMPDIFF</strong></span>
</p>
<p>
<code class="literal">TIMESTAMPDIFF ( &lt;tsi datetime field&gt;, &lt;datetime
      value expr 1&gt;, &lt;datetime value expr 2&gt;)</code>
</p>
<p>
<code class="literal">&lt;tsi datetime field&gt; ::= SQL_TSI_FRAC_SECOND |
      SQL_TSI_SECOND | SQL_TSI_MINUTE | SQL_TSI_HOUR | SQL_TSI_DAY |
      SQL_TSI_WEEK | SQL_TSI_MONTH | SQL_TSI_QUARTER |
      SQL_TSI_YEAR</code>
</p>
<p>HyperSQL supports full SQL Standard datetime features. It supports
      adding integers representing units of time directly to datetime values
      using the arithmetic plus operator. It also supports subtracting one
      <code class="literal">&lt;datetime value expr&gt;</code> from another in the given
      units of days using the minus operator. An example of
      <code class="literal">&lt;datetime value expr&gt; + &lt;numeric value expression&gt;
      &lt;datetime field&gt; </code>is <code class="literal">LOCALTIMESTAMP + 5
      DAY</code>. An example of <code class="literal">( &lt;datetime value expr&gt; -
      &lt;numeric value expression&gt; ) &lt;datetime field&gt; </code>is
      <code class="literal">(CURRENT_DATE - DATE '2008-08-8') MONTH </code>which returns
      the number of calendar months between the two dates.</p>
<p>The two JDBC functions, <code class="literal">TIMESTAMPADD </code>and
      <code class="literal">TIMESTAMPDIFF</code> perform the same function as above SQL
      expressions. The field names are keywords and are different from those
      used in the EXTRACT functions. These names are valid for use only when
      calling these two functions. The return value for TIMESTAMPADD is of the
      same type as the datetime argument used. The return type for
      TIMESTAMPDIFF is always BIGINT, regardless of the type of arguments. The
      two datetime arguments of TIMESTAMPDIFF should be of the same
      type.</p>
<p>
<code class="literal">TIMESTAMPDIFF</code> is evaluated as &lt;datetime
      value expr 2&gt; - &lt;datetime value expr 1&gt;.</p>
<p>(JDBC)</p>
<a name="N13B6B" class="indexterm"></a>
<p>
<span class="bold"><strong>DATEADD</strong></span>
</p>
<p>
<code class="literal">DATEADD ( &lt;field&gt;, &lt;numeric value expr&gt;,
      &lt;datetime value expr&gt; )</code>
</p>
<a name="N13B77" class="indexterm"></a>
<p>
<span class="bold"><strong>DATEDIFF</strong></span>
</p>
<p>
<code class="literal">DATEDIFF ( &lt;field&gt;, &lt;datetime value expr 1&gt;,
      &lt;datetime value expr 2&gt; )</code>
</p>
<p>
<code class="literal">&lt;field&gt; ::= 'yy' | 'year' | 'mm' | 'month' | 'dd' |
      'day' | 'hh' | 'hour' | 'mi' | 'minute' | 'ss' | 'second' | 'ms' |
      'millisecond'</code>
</p>
<p>The DATEADD and DATEDIFF functions are alternatives to
      TIMESTAMPADD and TIMESTAMPDIFF, with fewer available field options. The
      field names are specified as strings, rather than keywords. The fields
      translate to YEAR, MONTH, DAY, HOUR, MINUTE, SECOND and MILLISECOND.
      <code class="literal">DATEDIFF</code> is evaluated as &lt;datetime value expr
      2&gt; - &lt;datetime value expr 1&gt;. (HyperSQL}</p>
<p>
<code class="literal">DATEDIFF ( &lt;datetime value expr 1&gt;, &lt;datetime
      value expr 2&gt; )</code>
</p>
<p>This special form of <code class="literal">DATEDIFF</code> does not have a
      field parameter and return the number of days between two dates. This
      form is evaluated as <code class="literal">&lt;datetime value expr 1&gt; -
      &lt;datetime value expr 2&gt;</code>, which is different from the
      main form. This form is compatible with some other database engines.
      (HyperSQL}</p>
<a name="N13B96" class="indexterm"></a>
<p>
<span class="bold"><strong>ROUND</strong></span>
</p>
<p>
<code class="literal">ROUND ( &lt;datetime value expr&gt; [ , &lt;char value
      expr&gt; ] ) </code>
</p>
<p>The <code class="literal">&lt;datetime value expr&gt; </code>is of DATE,
      TIME or TIMESTAMP type. The <code class="literal">&lt;char value expr&gt;</code>
      is a format string for YEAR, MONTH, WEEK OF YEAR, DAY, HOUR, MINUTE or
      SECOND as listed in the table for TO_CHAR and TO_DATE format elements
      (see below). The datetime value is rounded up or down after the
      specified field and the rest of the fields to the right are set to one
      for MONTH and DAY, or zero, for the rest of the fields. For example
      rounding a timestamp value on the DAY field results in midnight the same
      date or midnight the next day if the time is at or after 12 noon. If the
      second argument is omitted, the datetime value is rounded to the
      nearest day. (HyperSQL)</p>
<a name="N13BAA" class="indexterm"></a>
<p>
<span class="bold"><strong>TRUNC</strong></span>
</p>
<p>
<code class="literal">TRUNC ( &lt;datetime value expr&gt; [ , &lt;char value
      expr&gt; ] ) </code>
</p>
<p>Similar to the ROUND function, the <code class="literal">&lt;num value expr&gt;
      </code>is of DATE, TIME or TIMESTAMP type. The <code class="literal">&lt;char
      value expr&gt;</code> is a format string for YEAR, MONTH, WEEK OF
      YEAR, DAY, HOUR, MINUTE or SECOND as listed in the table for TO_CHAR and
      TO_DATE format elements (see above). The datetime value is truncated
      after the specified field and the rest of the fields to the right are
      set to one for MONTH and DAY, or zero, for the rest of the fields. For
      example applying TRUNC to a timestamp value on the DAY field results in
      midnight the same date. Examples of ROUND and TRUNC functions are given
      below. If the second argument is omitted, the datetime value is
      truncated to midnight the same date. (HyperSQL)</p>
<pre class="programlisting">ROUND ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' )

'2009-01-01 00:00:00'

TRUNC ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' )

'2008-01-01 00:00:00'
</pre>
</div>
<div class="section" title="Functions to Convert or Format a Datetime">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="bfc_datetime_format"></a>Functions to Convert or Format a Datetime</h3>
</div>
</div>
</div>
<a name="N13BC4" class="indexterm"></a>
<p>
<span class="bold"><strong>TIMESTAMP</strong></span>
</p>
<p>TIMESTAMP ( &lt;num value expr&gt; )</p>
<p>TIMESTAMP ( &lt;char value expr&gt; )</p>
<p>
<code class="literal">TIMESTAMP ( &lt;char value expr&gt;, &lt;char value
      expr&gt; )</code>
</p>
<p>
<code class="literal">TIMESTAMP ( &lt;date value expr&gt;, &lt;time value
      expr&gt; )</code>
</p>
<p>This function translates the arguments into a TIMESTAMP value.
      When the single argument is a numeric value, it is interpreted as a Unix
      timestamp in seconds. A single formatted date or timestamp string is
      translated to a TIMESTAMP.</p>
<p>When two arguments are used, the first argument is the date part
      and the second argument is the time part of the returned TIMESTAMP
      value. An example, including the result, is given below:</p>
<pre class="programlisting">TIMESTAMP ( '2008-11-22', '20:30:40' )

TIMESTAMP '2008-11-22 20:30:40.000000'
</pre>
<a name="N13BDD" class="indexterm"></a>
<p>
<span class="bold"><strong>TO_CHAR</strong></span>
</p>
<p>
<code class="literal">TO_CHAR ( &lt;datetime value expr&gt;, &lt;char value
      expr&gt; )</code>
</p>
<p>This function formats a datetime or numeric value to the format
      given in the second argument. The format string can contain pattern
      elements from the list given below, plus punctuation and space
      characters. An example, including the result, is given below:</p>
<pre class="programlisting">TO_CHAR ( TIMESTAMP'2008-02-01 20:30:40', 'YYYY BC MONTH, DAY HH' )

2008 AD February, Friday 8
</pre>
<p>The format is internally translated to a
      <code class="classname">java.text.SimpleDateFormat</code> format string.
      Separator characters (space, comma, period, hyphen, colon, semicolon,
      forward slash) can be included between the pattern elements. Unsupported
      format strings should not be used. You can include a string literal
      inside the format string by enclosing it in double quotes.
      (HyperSQL)</p>
<a name="N13BF2" class="indexterm"></a>
<p>
<span class="bold"><strong>TO_DATE</strong></span>
</p>
<p>
<code class="literal">TO_DATE ( &lt;char value expr&gt;, &lt;char value
      expr&gt; )</code>
</p>
<p>This function translates a formatted datetime sting to a DATE
      according to the format given in the second argument. See TO_TIMESTAMP
      below for further details.</p>
<a name="N13C00" class="indexterm"></a>
<p>
<span class="bold"><strong>TO_TIMESTAMP</strong></span>
</p>
<p>
<code class="literal">TO_TIMESTAMP ( &lt;char value expr&gt;, &lt;char value
      expr&gt; )</code>
</p>
<p>This function translates a formatted datetime sting to a TIMESTAMP
      according to the format given in the second argument. The format string
      can contain pattern elements from the list given below, plus punctuation
      and space characters. The pattern should contain all the necessary
      fields to construct a date, including, year, month, day of month, etc.
      The returned timestamp can then be cast into DATE or TIME types if
      necessary. An example, including the result, is given below:</p>
<pre class="programlisting">TO_TIMESTAMP ( '22/11/2008 20:30:40', 'DD/MM/YYYY HH:MI:SS' )

TIMESTAMP '2008-11-22 20:30:40.000000'
</pre>
<p>The format strings that can be used for TO_DATE and TO_TIMESTAMP
      are more restrictive than those used for TO_CHAR, because the format
      string must contain all the elements needed to build a full DATE or
      TIMESTAMP value. The format is internally translated to a
      <code class="classname">java.text.SimpleDateFormat</code> format string.
      Unsupported format strings should not be used. You can include a string
      literal inside the format string by enclosing it in double quotes.
      (HyperSQL)</p>
<p>The supported format components are all uppercase as
      follows:</p>
<div class="table">
<a name="N13C17"></a>
<p class="title">
<b>Table&nbsp;10.1.&nbsp;TO_CHAR, TO_DATE and TO_TIMESTAMP format elements</b>
</p>
<div class="table-contents">
<table summary="TO_CHAR, TO_DATE and TO_TIMESTAMP format elements" cellspacing="0" style="border-collapse: collapse;border-top: 0.5pt solid ; border-bottom: 0.5pt solid ; border-left: 0.5pt solid ; border-right: 0.5pt solid ; ">
<colgroup>
<col width="5cm">
<col>
</colgroup>
<tbody>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">BC | B.C. | AD | A.D.</code></td><td style="border-bottom: 0.5pt solid ; ">Returns <code class="literal">AD</code> for common era and
              <code class="literal">BC</code> for before common era</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">RRRR</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>4-digit year</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">YYYY</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>4-digit year</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">IYYY</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>4-digit year, corresponding to ISO week of the
              year. The reported year for the last few days of the calendar
              year may be the next year.</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">YY</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>2 digit year</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">IY</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>2 digit year, corresponding to ISO week of the year
              </p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">MM</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Month (01-12)</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">MON</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Short three-letter name of month</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">MONTH</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Name of month</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">WW</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Week of year (1-53) where week 1 starts on the
              first day of the year and continues to the seventh day of the
              year.</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">W</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Week of month (1-5) where week 1 starts on the
              first day of the month and ends on the seventh.</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">IW</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Week of year (1-52 or 1-53) based on the ISO
              standard. Week starts on Monday. The first week may start near
              the end of previous year.</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">DAY</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Name of day.</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">DD</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Day of month (01-31).</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">DDD</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Day of year (1-366).</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">DY</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Short three-letter name of day.</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">HH</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Hour of day (00-11).</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">HH12</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Hour of day (00-11).</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">HH24</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Hour of day (00-23).</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">MI</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Minute (00-59).</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">SS</code></td><td style="border-bottom: 0.5pt solid ; ">
<p>Second (00-59).</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; "><code class="literal">FF</code></td><td style="">
<p>Fractional seconds.</p>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<br class="table-break">
</div>
</div>
<div class="section" title="Array Functions">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="bfc_array_functions"></a>Array Functions</h2>
</div>
</div>
</div>
<p>Array functions are specialised functions with ARRAY parameters or
    return values. For the ARRAY_AGG aggregate function, see the <a class="link" href="dataaccess-chapt.html" title="Chapter&nbsp;7.&nbsp;Data Access and Change">Data Access and Change</a>
    chapter.</p>
<a name="N13CCC" class="indexterm"></a>
<p>
<span class="bold"><strong>CARDINALITY</strong></span>
</p>
<p>
<code class="literal">CARDINALITY( &lt;array value expr&gt; )</code>
</p>
<p>Returns the element count for the given array argument.
    (Foundation)</p>
<a name="N13CDA" class="indexterm"></a>
<p>
<span class="bold"><strong>MAX_CARDINALITY</strong></span>
</p>
<p>
<code class="literal">MAX_CARDINALITY( &lt;array value expr&gt;
    )</code>
</p>
<p>Returns the maximum allowed element count for the given array
    argument. (Foundation)</p>
<a name="N13CE8" class="indexterm"></a>
<p>
<span class="bold"><strong>POSITION_ARRAY</strong></span>
</p>
<p>
<code class="literal">POSITION_ARRAY( &lt;value expression&gt; IN &lt;array value
    expr&gt; [ FROM &lt;int value expr&gt; ] )</code>
</p>
<p>Returns the position of the first match for the <code class="literal">&lt;value
    expression&gt;</code> in the array. By default the search starts from
    the beginning of the array. The optional <code class="literal">&lt;int value
    expr&gt;</code> specifies the start position. Positions are counted
    from 1. Returns zero if no match is found. (HyperSQL)</p>
<a name="N13CFC" class="indexterm"></a>
<p>
<span class="bold"><strong>SORT_ARRAY</strong></span>
</p>
<p>
<code class="literal">SORT_ARRAY( &lt;array value expr&gt; [ { ASC | DESC } ] [
    NULLS { FIRST | LAST } ] )</code>
</p>
<p>Returns a sorted copy of the array. By default, sort is performed in
    ascending order and NULL elements are sorted first. (HyperSQL)</p>
<a name="N13D0A" class="indexterm"></a>
<p>
<span class="bold"><strong>TRIM_ARRAY</strong></span>
</p>
<p>
<code class="literal">TRIM_ARRAY( &lt;array value expr&gt;, &lt;num value
    expr&gt; )</code>
</p>
<p>Returns a new array that contains the elements of the
    <code class="literal">&lt;array value expr&gt;</code> minus the number of elements
    specified by the <code class="literal">&lt;num value expr&gt;. </code>Elements are
    discarded from the end of the array. (Foundation)</p>
<a name="N13D1E" class="indexterm"></a>
<p>
<span class="bold"><strong>SEQUENCE_ARRAY</strong></span>
</p>
<p>
<code class="literal">SEQUENCE_ARRAY( &lt;value expr 1&gt;, &lt;value expr 2&gt;,
    &lt;value expr 3 )</code>
</p>
<p>Returns a new array that contains a sequence of values. The
    <code class="literal">&lt;value expr 1&gt;</code> is the lower bound of the range.
    The <code class="literal">&lt;value expr 2&gt;</code> is the upper bound of the
    range. The <code class="literal">&lt;value expr 3&gt;</code> is the increment. The
    elments of the array are within the inclusive range. The first element is
    <code class="literal">&lt;value expr 1&gt;</code> and each subsequent element is the
    sum of the previous element and the increment. If the increment is zero,
    only the first element is returned. When the increment is negative, the
    lower bound should be larger than the upper bound. The type of the
    arguments can be all number types, or a datetime range and an interval for
    the third argument (HyperSQL)</p>
<p>In the examples below, a number sequence and a date sequence are
    shown. The UNNEST table expression is used to form a table from the
    array.</p>
<pre class="programlisting">SEQUENCE_ARRAY(0, 100, 5)

ARRAY[0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100] 

SELECT * FROM UNNEST(SEQUENCE_ARRAY(10, 12, 1))

C1 
-- 
10 
11 
12 

SELECT * FROM UNNEST(SEQUENCE_ARRAY(CURRENT_DATE, CURRENT_DATE + 6 DAY, 1 DAY)) WITH ORDINALITY AS T(D, I) 

D          I 
---------- - 
2010-08-01 1 
2010-08-02 2 
2010-08-03 3 
2010-08-04 4 
2010-08-05 5 
2010-08-06 6 
2010-08-07 7

</pre>
</div>
<div class="section" title="General Functions">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="bfc_general_functions"></a>General Functions</h2>
</div>
</div>
</div>
<p>General functions can take different types of arguments. Some
    General Functions accept a variable number of arguments.</p>
<a name="N13D42" class="indexterm"></a>
<p>
<span class="bold"><strong>COALESCE</strong></span>
</p>
<p>
<code class="literal">COALESCE( &lt;value expr 1&gt;, &lt;value expr 2&gt; [,
    ...] )</code>
</p>
<p>Returns <code class="literal">&lt;value expr 1&gt;</code> if it is not null,
    otherwise returns <code class="literal">&lt;value expr 2&gt;</code> if not null and
    so on. The type of both arguments must be comparable. (Foundation)</p>
<a name="N13D56" class="indexterm"></a>
<p>
<span class="bold"><strong>CONVERT</strong></span>
</p>
<p>
<code class="literal">CONVERT ( &lt;value expr&gt; , &lt;data type&gt;
    )</code>
</p>
<p>
<code class="literal">&lt;data type&gt; ::= { SQL_BIGINT | SQL_BINARY | SQL_BIT
    |SQL_BLOB | SQL_BOOLEAN | SQL_CHAR | SQL_CLOB | SQL_DATE | SQL_DECIMAL |
    SQL_DATALINK |SQL_DOUBLE | SQL_FLOAT | SQL_INTEGER | SQL_LONGVARBINARY |
    SQL_LONGNVARCHAR | SQL_LONGVARCHAR | SQL_NCHAR | SQL_NCLOB | SQL_NUMERIC |
    SQL_NVARCHAR | SQL_REAL | SQL_ROWID | SQL_SQLXML | SQL_SMALLINT | SQL_TIME
    | SQL_TIMESTAMP | SQL_TINYINT | SQL_VARBINARY | SQL_VARCHAR} [ (
    &lt;precision, length or scale parameters&gt; ) ]</code>
</p>
<p>The CONVERT function is a JDBC escape function, equivalent to the
    SQL standard CAST expression. It converts the <code class="literal">&lt;value
    expr&gt;</code> into the given <code class="literal">&lt;data type&gt;</code> and
    returns the value. The <code class="literal">&lt;data type&gt;</code> options are
    synthetic names made by prefixing type names with <code class="literal">SQL_</code>.
    Some of the <code class="literal">&lt;data type&gt;</code> options represent valid
    SQL types, but some are based on non-standard type names, namely
    <code class="literal">{ SQL_LONGNVARCHAR | SQL_LONGVARBINARY |SQL_LONGVARCHAR |
    SQL_TINYINT }</code>. None of the synthetic names can be used in any
    other context than the CONVERT function.</p>
<p>The definition of CONVERT in the JDBC Standard does not allow the
    precision, scale or length to be specified. This is required by the SQL
    standard for BINARY, BIT, BLOB, CHAR, CLOB, VARBINARY and VARCHAR types
    and is often needed for DECIMAL and NUMERIC. Defaults are used for
    precision.</p>
<p>HyperSQL also allows the use of real type names (without the
    <code class="literal">SQL_</code> prefix). In this usage, HyperSQL allows the use of
    precision, scale or length for the type definition when they are valid for
    the type definition.</p>
<p>When MS SQL Server compatibility mode is on, the parameters of
    CONVERT are switched and only the real type names with required precision,
    scale or length are allowed. (JDBC)</p>
<a name="N13D82" class="indexterm"></a>
<p>
<span class="bold"><strong>DECODE</strong></span>
</p>
<p>
<code class="literal">DECODE( &lt;value expr main&gt;, &lt;value expr match
    1&gt;, &lt;value expr result 1&gt; [...,] [, &lt;value expr default&gt;]
    )</code>
</p>
<p>DECODE takes at least 3 arguments. The <code class="literal">&lt;value expr
    main&gt;</code> is compared with <code class="literal">&lt;value expr match
    1&gt;</code> and if it matches, <code class="literal">&lt;value expr result
    1&gt;</code> is returned. If there are additional pairs of
    <code class="literal">&lt;value expr match n&gt;</code> and <code class="literal">&lt;value expr
    result n&gt;</code>, comparison is repeated until a match is found the
    result is returned. If no match is found, the <code class="literal">&lt;value expr
    default&gt;</code> is returned if it is specified, otherwise NULL is
    returned. The type of the return value is a combination of the types of
    the <code class="literal">&lt;value expr result ... &gt;</code> arguments.
    (HyperSQL)</p>
<a name="N13DA5" class="indexterm"></a>
<p>
<span class="bold"><strong>GREATEST</strong></span>
</p>
<p>
<code class="literal">GREATEST( &lt;value expr 1&gt;, [&lt;value expr ...&gt;,
    ...] )</code>
</p>
<p>The GREATEST function takes one or more arguments. It compares the
    arguments with each other and returns the greatest argument. The return
    type is the combined type of the arguments. Arguments can be of any type,
    so long as they are comparable. (HyperSQL)</p>
<a name="N13DB3" class="indexterm"></a>
<p>
<span class="bold"><strong>IFNULL</strong></span>
</p>
<a name="N13DBC" class="indexterm"></a>
<p>
<span class="bold"><strong>ISNULL</strong></span>
</p>
<p>
<code class="literal">IFNULL | ISNULL ( &lt;value expr 1&gt;, &lt;value expr
    2&gt; )</code>
</p>
<p>Returns <code class="literal">&lt;value expr 1&gt;</code> if it is not null,
    otherwise returns <code class="literal">&lt;value expr 2&gt;</code>. The type of the
    return value is the type of <code class="literal">&lt;value expr 1&gt;</code>.
    Almost equivalent to SQL Standard <code class="literal">COALESCE(&lt;value expr 1&gt;,
    &lt;value expr 2&gt;)</code> function, but without type modification.
    (JDBC)</p>
<a name="N13DD6" class="indexterm"></a>
<p>
<span class="bold"><strong>LEAST</strong></span>
</p>
<p>
<code class="literal">LEAST( &lt;value expr 1&gt;, [&lt;value expr ...&gt;, ...]
    )</code>
</p>
<p>The LEAST function takes one or more arguments. It compares the
    arguments with each other and returns the smallest argument. The return
    type is the combined type of the arguments. Arguments can be of any type,
    so long as they are comparable. (HyperSQL)</p>
<a name="N13DE4" class="indexterm"></a>
<p>
<span class="bold"><strong>LOAD_FILE</strong></span>
</p>
<p>
<code class="literal">LOAD_FILE ( &lt;char value expr 1&gt; [, &lt;char value
    expr 2&gt;] )</code>
</p>
<p>Returns a BLOB or CLOB containing the URL or file path specified in
    the first argument. If used with a single argument, the function returns a
    BLOB. If used with two arguments, the function returns a CLOB and the
    second argument is the character encoding of the file. (HyperSQL)</p>
<a name="N13DF2" class="indexterm"></a>
<p>
<span class="bold"><strong>NULLIF</strong></span>
</p>
<p>
<code class="literal">NULLIF( &lt;value expr 1&gt;, &lt;value expr 2&gt;
    )</code>
</p>
<p>Returns <code class="literal">&lt;value expr 1&gt;</code> if it is not equal
    to <code class="literal">&lt;value expr 2&gt;</code>, otherwise returns null. The
    type of both arguments must be the same. This function is a shorthand for
    a specific CASE expression. (Foundation)</p>
<a name="N13E06" class="indexterm"></a>
<p>
<span class="bold"><strong>NVL</strong></span>
</p>
<p>
<code class="literal">NVL( &lt;value expr 1&gt;, &lt;value expr 2&gt;
    )</code>
</p>
<p>Returns <code class="literal">&lt;value expr 1&gt;</code> if it is not null,
    otherwise returns <code class="literal">&lt;value expr 2&gt;</code>. The type of the
    return value is the type of <code class="literal">&lt;value expr 1&gt;</code>. For
    example, if <code class="literal">&lt;value expr 1&gt;</code> is an INTEGER column
    and <code class="literal">&lt;value expr 2&gt;</code> is a DOUBLE constant, the
    return type is cast into INTEGER. This function is similar to IFNULL.
    (HyperSQL)</p>
<a name="N13E23" class="indexterm"></a>
<p>
<span class="bold"><strong>NVL2</strong></span>
</p>
<p>
<code class="literal">NVL2( &lt;value expr 1&gt;, &lt;value expr 2&gt;, &lt;value
    expr 3&gt; )</code>
</p>
<p>If <code class="literal">&lt;value expr 1&gt;</code> is not null, returns
    <code class="literal">&lt;value expr 2&gt;</code>, otherwise returns
    <code class="literal">&lt;value expr 3&gt;</code>. The type of the return value is
    the type of <code class="literal">&lt;value expr 2&gt;</code> unless it is null.
    (HyperSQL)</p>
<a name="N13E3D" class="indexterm"></a>
<p>
<span class="bold"><strong>UUID</strong></span>
</p>
<p>
<code class="literal">UUID ( [ { &lt;char value expr&gt; | &lt;binary value
    expr&gt; ] } ) </code>
</p>
<p>With no parameter, this function returns a new UUID value as a 16
    byte binary value. With a UUID hexadecimal string argument, it returns the
    16 byte binary value of the UUID. With a 16 byte binary argument, it
    returns the formatted UUID character representation. (HyperSQL)</p>
</div>
<div class="section" title="System Functions">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="bfc_system_functions"></a>System Functions</h2>
</div>
</div>
</div>
<a name="N13E4F" class="indexterm"></a>
<p>
<span class="bold"><strong>CRYPT_KEY</strong></span>
</p>
<p>
<code class="literal">CRYPT_KEY( &lt;value expr 1&gt;, &lt;value expr 2&gt;
    )</code>
</p>
<p>Returns a binary string representation of a cryptography key for the
    given cipher and cryptography provider. The cipher specification is
    specified by <code class="literal">&lt;value expr 1&gt;</code> and the provider by
    <code class="literal">&lt;value expr 2&gt;</code>. To use the default provider,
    specify null for <code class="literal">&lt;value expr 2&gt;</code>.
    (HyperSQL)</p>
<a name="N13E66" class="indexterm"></a>
<p>
<span class="bold"><strong>DIAGNOSTICS</strong></span>
</p>
<p>
<code class="literal">DIAGNOSTICS ( ROW_COUNT )</code>
</p>
<p>This is a convenience function for use instead of the <code class="literal">GET
    DIAGNOSTICS ...</code> statement. The argument specifies the name of
    the diagnostics variable. Currently the only supported variable is the
    <code class="literal">ROW_COUNT</code> variable. The function returns the row count
    returned by the last executed statement. The return value is 0 after most
    statements. Calling this function immediately after executing an INSERT,
    UPDATE, DELETE or MERGE statement returns the row count for the last
    statement, as it is returned by the JDBC statement. (HyperSQL)</p>
<a name="N13E7A" class="indexterm"></a>
<p>
<span class="bold"><strong>IDENTITY</strong></span>
</p>
<p>
<code class="literal">IDENTITY ()</code>
</p>
<p>Returns the last IDENTITY value inserted into a row by the current
    session. The statement, CALL IDENTITY() can be made after an INSERT
    statement that inserts a row into a table with an IDENTITY column. The
    CALL IDENTITY() statement returns the last IDENTITY value that was
    inserted into a table by the current session. Each session manages this
    function call separately and is not affected by inserts in other sessions.
    The statement can be executed as a direct statement or a prepared
    statement. (HyperSQL)</p>
<a name="N13E88" class="indexterm"></a>
<p>
<span class="bold"><strong>DATABASE</strong></span>
</p>
<p>
<code class="literal">DATABASE ()</code>
</p>
<p>Returns the file name (without directory information) of the
    database. (JDBC)</p>
<a name="N13E96" class="indexterm"></a>
<p>
<span class="bold"><strong>DATABASE_NAME</strong></span>
</p>
<p>
<code class="literal">DATABASE_NAME ()</code>
</p>
<p>Returns the database name. This name is a 16 character, uppercase
    string. It is generated as a string based on the timestamp of the creation
    of the database, for example HSQLDB32438AEAFB. The name can be redefined
    by an admin user but the new name must be all uppercase and 16 characters
    long. This name is used in log messages with external logging frameworks.
    (HyperSQL)</p>
<a name="N13EA4" class="indexterm"></a>
<p>
<span class="bold"><strong>DATABASE_VERSION</strong></span>
</p>
<p>
<code class="literal">DATABASE_VERSION ()</code>
</p>
<p>Returns the full version string for the database engine. For
    example, 2.0.1. (JDBC)</p>
<a name="N13EB2" class="indexterm"></a>
<p>
<span class="bold"><strong>USER</strong></span>
</p>
<p>
<code class="literal">USER ()</code>
</p>
<p>Equivalent to the SQL function <code class="literal">CURRENT_USER</code>.
    (JDBC)</p>
<a name="N13EC3" class="indexterm"></a>
<p>
<span class="bold"><strong>CURRENT_USER</strong></span>
</p>
<p>
<code class="literal">CURRENT_USER</code>
</p>
<a name="N13ECF" class="indexterm"></a>
<p>
<span class="bold"><strong>CURRENT_ROLE</strong></span>
</p>
<p>
<code class="literal">CURRENT_ROLE</code>
</p>
<a name="N13EDB" class="indexterm"></a>
<p>
<span class="bold"><strong>SESSION_USER</strong></span>
</p>
<p>
<code class="literal">SESSION_USER</code>
</p>
<a name="N13EE7" class="indexterm"></a>
<p>
<span class="bold"><strong>SYSTEM_USER</strong></span>
</p>
<p>
<code class="literal">SYSTEM_USER</code>
</p>
<a name="N13EF3" class="indexterm"></a>
<p>
<span class="bold"><strong>CURRENT_SCHEMA</strong></span>
</p>
<p>
<code class="literal">CURRENT_SCHEMA</code>
</p>
<a name="N13EFF" class="indexterm"></a>
<p>
<span class="bold"><strong>CURRENT_CATALOG</strong></span>
</p>
<p>
<code class="literal">CURRENT_CATALOG</code>
</p>
<p>These functions return the named current session attribute. They are
    all SQL Standard functions.</p>
<p>The CURRENT_USER is the user that connected to the database, or a
    user subsequently set by the SET AUTHORIZATION statement.</p>
<p>SESSION_USER is the same as CURRENT_USER</p>
<p>SYSTEM_USER is the user that connected to the database. It is not
    changed with any command until the session is closed.</p>
<p>CURRENT_SCHEMA is default schema of the user, or a schema
    subsequently set by the SET SCHEMA command.</p>
<p>CURRENT_CATALOG is always the same within a given HyperSQL database
    and indicates the name of the catalog.</p>
<a name="N13F17" class="indexterm"></a>
<p>
<span class="bold"><strong>IS_AUTOCOMMIT</strong></span>
</p>
<p>
<code class="literal">IS_AUTOCOMMIT()</code>
</p>
<p>Returns TRUE if the session is in autocommit mode. (HyperSQL)</p>
<a name="N13F25" class="indexterm"></a>
<p>
<span class="bold"><strong>IS_READONLY_SESSION</strong></span>
</p>
<p>
<code class="literal">IS_READONLY_SESSION()</code>
</p>
<p>Returns TRUE if the session is in read only mode. (HyperSQL)</p>
<a name="N13F33" class="indexterm"></a>
<p>
<span class="bold"><strong>IS_READONLY_DATABASE</strong></span>
</p>
<p>
<code class="literal">IS_READONLY_DATABASE()</code>
</p>
<p>Returns TRUE if the database is a read only database.
    (HyperSQL)</p>
<a name="N13F41" class="indexterm"></a>
<p>
<span class="bold"><strong>IS_READONLY_DATABASE_FILES</strong></span>
</p>
<p>
<code class="literal">IS_READONLY_DATABASE_FILES()</code>
</p>
<p>Returns TRUE if the database is a read-only files database. In this
    kind of database, it is possible to modify the data, but the changes are
    not persisted to the database files. (HyperSQL)</p>
<a name="N13F4F" class="indexterm"></a>
<p>
<span class="bold"><strong>ISOLATION_LEVEL</strong></span>
</p>
<p>
<code class="literal">ISOLATION_LEVEL()</code>
</p>
<p>Returns the current transaction isolation level for the session.
    Returns either READ COMMITTED or SERIALIZABLE as a string.
    (HyperSQL)</p>
<a name="N13F5D" class="indexterm"></a>
<p>
<span class="bold"><strong>SESSION_ID</strong></span>
</p>
<p>
<code class="literal">SESSION_ID()</code>
</p>
<p>Returns the id of the session as a BIGINT value. Each session id is
    unique during the operational lifetime of the database. Id's are restarted
    after a shutdown and restart. (HyperSQL)</p>
<a name="N13F6B" class="indexterm"></a>
<p>
<span class="bold"><strong>SESSION_ISOLATION_LEVEL</strong></span>
</p>
<p>
<code class="literal">SESSION_ISOLATION_LEVEL()</code>
</p>
<p>Returns the default transaction isolation level for the current
    session. Returns either READ COMMITTED or SERIALIZABLE as a string.
    (HyperSQL)</p>
<a name="N13F79" class="indexterm"></a>
<p>
<span class="bold"><strong>DATABASE_ISOLATION_LEVEL</strong></span>
</p>
<p>
<code class="literal">DATABASE_ISOLATION_LEVEL()</code>
</p>
<p>Returns the default transaction isolation level for the database.
    Returns either READ COMMITTED or SERIALIZABLE as a string.
    (HyperSQL)</p>
<a name="N13F87" class="indexterm"></a>
<p>
<span class="bold"><strong>TRANSACTION_SIZE</strong></span>
</p>
<p>
<code class="literal">TRANSACTION_SIZE()</code>
</p>
<p>Returns the row change count for the current transaction. Each row
    change represents a row INSERT or a row DELETE operation. There will be a
    pair of row change operations for each row that is updated.</p>
<a name="N13F95" class="indexterm"></a>
<p>
<span class="bold"><strong>TRANSACTION_ID</strong></span>
</p>
<p>
<code class="literal">TRANSACTION_ID()</code>
</p>
<p>Returns the current transaction ID for the session as a BIGINT
    value. The database maintains a global incremental id which is allocated
    to new transactions and new actions (statement executions) in different
    sessions. This value is unique to the current transaction.
    (HyperSQL)</p>
<a name="N13FA3" class="indexterm"></a>
<p>
<span class="bold"><strong>ACTION_ID</strong></span>
</p>
<p>
<code class="literal">ACTION_ID()</code>
</p>
<p>Returns the current action ID for the session as a BIGINT value. The
    database maintains a global incremental id which is allocated to new
    transactions and new actions (statement executions) in different sessions.
    This value is unique to the current action. (HyperSQL)</p>
<a name="N13FB1" class="indexterm"></a>
<p>
<span class="bold"><strong>TRANSACTION_CONTROL</strong></span>
</p>
<p>
<code class="literal">TRANSACTION_CONTROL()</code>
</p>
<p>Returns the current transaction model for the database. Returns
    LOCKS, MVLOCKS or MVCC as a string. (HyperSQL)</p>
<a name="N13FBF" class="indexterm"></a>
<p>
<span class="bold"><strong>LOB_ID</strong></span>
</p>
<p>
<code class="literal">LOB_ID( &lt;column reference&gt; )</code>
</p>
<p>Returns internal ID of a lob as a BIGINT value. Lob ID's are unique
    and never reused. The &lt;column reference&gt; is the name of the column
    (or variable, or argument) which is a CLOB or BLOB. Returns null if the
    value is null. (HyperSQL)</p>
<a name="N13FCD" class="indexterm"></a>
<p>
<span class="bold"><strong>ROWNUM</strong></span>
</p>
<p>
<code class="literal">ROWNUM()</code>
</p>
<p>Returns the current row number (from 1) being processed in a select
    statement. This has the same semantics as the ROWNUM pseudo-column in
    Oracle syntax mode, but can be used in any syntax mode. The function is
    always used in a SELECT statement. The ROWNUM of a row is incremented as
    the rows are added to the result set. It is therefore possible to use a
    condition such as WHERE ROWNUM() &lt; 10, but not ROWNUM() &gt; 10 or
    ROWNUM = 10. (HyperSQL)</p>
</div>
</div>
<HR xmlns:xi="http://www.w3.org/2001/XInclude">
<P xmlns:xi="http://www.w3.org/2001/XInclude" class="svnrev">$Revision: 4864 $</P>
<div class="navfooter">
<hr>
<table summary="Navigation footer" width="100%">
<tr>
<td align="left" width="40%"><a accesskey="p" href="triggers-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a>&nbsp;</td><td align="center" width="20%">&nbsp;</td><td align="right" width="40%">&nbsp;<a accesskey="n" href="management-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td>
</tr>
<tr>
<td valign="top" align="left" width="40%">Chapter&nbsp;9.&nbsp;Triggers&nbsp;</td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="40%">&nbsp;Chapter&nbsp;11.&nbsp;System Management</td>
</tr>
</table>
</div>
</body>
</html>
